DROP DATABASE IF EXISTS `db_test`;

CREATE DATABASE `db_test`;

USE `db_test`;

DROP TABLE IF EXISTS `tbl_PerformanceComparison`;

CREATE TABLE `tbl_PerformanceComparison` (
	`performanceComparison_sampleTime` DATETIME NOT NULL,
	`performanceComparison_sampleText` NVARCHAR(2000) NOT NULL
) PARTITION BY RANGE(YEAR(performanceComparison_sampleTime)) ( -- http://dba.stackexchange.com/questions/65665/partition-by-year-and-sub-partition-by-month-mysql
	PARTITION part_lessThan1990 VALUES LESS THAN (1990),
    PARTITION part_lessThan2000 VALUES LESS THAN (2000),
    PARTITION part_lessThan2010 VALUES LESS THAN (2010),
    PARTITION part_lessThanTheCreation VALUES LESS THAN MAXVALUE
);

DROP PROCEDURE IF EXISTS usp_fillPerformanceComparisonTable;
DELIMITER $$ -- http://stackoverflow.com/questions/10259504/delimiters-in-mysql
CREATE PROCEDURE usp_fillPerformanceComparisonTable()
BEGIN
	DECLARE @i INT DEFAULT 1;
	DECLARE @sampleTime DATETIME DEFAULT GETDATE();
	DECLARE @sampleText NVARCHAR(2000);

	WHILE @i <= 100000 -- 100 000
	BEGIN
		SET @sampleTime = DATEADD(hh, -1, @sampleTime) -- add '-1' hour
		SET @sampleText = 'Sample text - ' + CAST(@i AS NVARCHAR(MAX))

		INSERT INTO tbl_PerformanceComparison
			VALUES (@sampleTime, @sampleText)

		SET @i = @i + 1
	END WHILE;
END;

CALL usp_fillPerformanceComparisonTable();

SELECT * FROM tbl_PerformanceComparison
	WHERE performanceComparison_sampleTime = '2001-07-07';
DELIMITER ;